package com.ruoyi.dm.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.dm.domain.po.CollectTleRecordPO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface CollectTleRecordMapper extends BaseMapper<CollectTleRecordPO> {


    /**
     * 先查出有重复的时间和code,按时间code分组 取最大id
     * 查出重复时间和code 的id ,与上面取差集
     */
    @Delete(" DELETE FROM COLLECT_TLE_RECORD WHERE id IN (" +
            " SELECT id FROM COLLECT_TLE_RECORD r,(" +
            " SELECT TLE_CODE,TLE_TIME FROM  (" +
            " SELECT count(1) num,TLE_CODE,TLE_TIME FROM COLLECT_TLE_RECORD GROUP BY TLE_CODE,TLE_TIME  ) WHERE num>1 ) dd WHERE r.TLE_CODE  = dd.TLE_CODE AND r.TLE_TIME = dd.TLE_TIME " +
            " EXCEPT " +
            " SELECT max(id) FROM COLLECT_TLE_RECORD r,(" +
            " SELECT TLE_CODE,TLE_TIME FROM  (" +
            " SELECT count(1) num,TLE_CODE,TLE_TIME FROM COLLECT_TLE_RECORD GROUP BY TLE_CODE,TLE_TIME  ) WHERE num>1 ) dd WHERE r.TLE_CODE  = dd.TLE_CODE AND r.TLE_TIME = dd.TLE_TIME GROUP BY r.TLE_CODE,r.TLE_TIME " +
            ")")
    int repeatRemove();

}
